﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Runtime.InteropServices;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace SqlInterface
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            string sql = "SELECT * FROM userInfo";
            DataTable dt = DBHelper.GetDataTable(sql);
            dataGridView1.DataSource = dt;

            dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect; //选中整行
            dataGridView1.ReadOnly = true; //只读，禁止编辑数据
            dataGridView1.AllowUserToAddRows = false;


        }
        //查找
        private void Query_Click(object sender, EventArgs e)
        {
            string userName = inputName.Text;
            string sql = "SELECT * FROM userInfo WHERE userName LIKE '%" + userName + "%'";
            DataTable dt = DBHelper.GetDataTable(sql);
            dataGridView1.DataSource = dt;
        }

        //添加
        private void Add_Click(object sender, EventArgs e)
        {
            EditForm ef = new EditForm();
            ef.ShowDialog();
            var res = ef.DialogResult;
            //当成功添加时重新刷新主窗口数据
            if (res == DialogResult.Yes)
            {
                string sql = "SELECT * FROM userInfo";
                DataTable dt = DBHelper.GetDataTable(sql);
                dataGridView1.DataSource = dt;
            }
        }

        //更新
        private void Update_Click(object sender, EventArgs e)
        {
            int userId = (int)dataGridView1.SelectedRows[0].Cells["userId"].Value;
            string userName = (string)dataGridView1.SelectedRows[0].Cells["userName"].Value;
            string userPwd = (string)dataGridView1.SelectedRows[0].Cells["userPwd"].Value;
            string userSex = (string)dataGridView1.SelectedRows[0].Cells["userSex"].Value;
            int userAge = (int)dataGridView1.SelectedRows[0].Cells["userAge"].Value;
            EditForm ef = new EditForm(userId , userName , userPwd , userSex , userAge);
            ef.ShowDialog();
            var res = ef.DialogResult;
            //当成功更新时重新刷新主窗口数据
            if (res == DialogResult.Yes)
            {
                string sql = "SELECT * FROM userInfo";
                DataTable dt = DBHelper.GetDataTable(sql);
                dataGridView1.DataSource = dt;
            }
        }

        //删除
        private void Delete_Click(object sender, EventArgs e)
        {
            var row = dataGridView1.SelectedRows[0];
            var cell = row.Cells[0];
            var userId = cell.Value;
            string delete = "DELETE FROM userInfo WHERE userId = " + userId;
            var res1 = DBHelper.EditData(delete);
            if(res1 == 1)
            {
                var b =  MessageBox.Show("确定删除该行数据吗？删除后将无法恢复！", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
                if (b == DialogResult.Yes)
                {
                    MessageBox.Show("删除成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    //重新刷新数据
                    string sql = "SELECT * FROM userInfo";
                    DataTable dt = DBHelper.GetDataTable(sql);
                    dataGridView1.DataSource = dt;
                }
                else
                {
                    MessageBox.Show("你取消了删除！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                /*
                MessageBox.Show("删除成功！","提示",MessageBoxButtons.OK,MessageBoxIcon.Information);
                //重新刷新数据
                string sql = "SELECT * FROM userInfo";
                DataTable dt = DBHelper.GetDataTable(sql);
                dataGridView1.DataSource = dt;
                */
            }
            else
            {
                MessageBox.Show("删除失败！","错误",MessageBoxButtons.OK,MessageBoxIcon.Error);
            }
        }

        private void truncate_Click(object sender, EventArgs e)
        {
            var b = MessageBox.Show("确定清空表数据吗？清空后将无法恢复！", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
            if (b == DialogResult.Yes)
            {
                string truncate = "TRUNCATE TABLE userInfo";
                DBHelper.EditData(truncate);
                MessageBox.Show("成功清空表数据！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                string sql = "SELECT * FROM userInfo";
                DataTable dt = DBHelper.GetDataTable(sql);
                dataGridView1.DataSource = dt;
            }
            else
            {
                MessageBox.Show("你取消了清空！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            
        }
    }
}
